package br.com.northwind.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Date;
import java.util.ArrayList;
import java.util.List;

import br.com.northwind.database.Conexao;
import br.com.northwind.exceptions.ConexaoException;
import br.com.northwind.exceptions.DriverException;
import br.com.northwind.fabricas.FabricaConexao;
import br.com.northwind.interfaces.IOrdersDao;
import br.com.northwind.tabelas.Orders;

public class OrdersDao implements IOrdersDao {

	private final String SQL_INSERT = "INSERT INTO Orders (CustomerId, EmployeeId, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
	private final String SQL_UPDATE = "UPDATE Orders SET CustomerId = ?, EmployeeId = ?, OrderDate = ?, RequiredDate = ?, ShippedDate = ?, ShipVia = ?, Freight = ?, ShipName = ?, ShipAddress = ?, ShipCity = ?, ShipRegion = ?, ShipPostalCode = ?, ShipCountry = ? WHERE OrderId = ?";
	private final String SQL_DELETE = "DELETE FROM Orders WHERE OrderId = ?";
	private final String SQL_SELECT_TODOS = "SELECT * FROM Orders";
	private final String SQL_SELECT_BY_CODIGO = "SELECT * FROM Orders WHERE OrderId = ?";
	
	private Conexao conexao;
	private PreparedStatement statement = null;
	private ResultSet rs = null;

	public OrdersDao() {
		this.conexao = FabricaConexao.criarConexao();
	}

	public OrdersDao(Conexao conexao) {
		this.conexao = conexao;
	}

	public void inserir(Orders _orderid) throws ConexaoException,DriverException {
		try {
			this.conexao.abrirConexao();
			this.statement = this.conexao.getConexao().prepareStatement(this.SQL_INSERT);
			
			this.statement.setString(1, _orderid.getCustomerId());
			this.statement.setInt(2, _orderid.getEmployeeId());
			this.statement.setDate(3, _orderid.getOrderDate());
			this.statement.setDate(4, _orderid.getRequiredDate());
			this.statement.setDate(5, _orderid.getShippedDate());
			this.statement.setInt(6, _orderid.getShipVia());
			this.statement.setDouble(7, _orderid.getFreight());
			this.statement.setString(8, _orderid.getShipName());
			this.statement.setString(9, _orderid.getShipAddress());
			this.statement.setString(10, _orderid.getShipCity());
			this.statement.setString(11, _orderid.getShipRegion());
			this.statement.setString(12, _orderid.getShipPostalCode());
			this.statement.setString(13, _orderid.getShipCountry());

			this.statement.execute();
		} catch (SQLException e) {
			throw new ConexaoException();
		} catch (ClassNotFoundException e) {
			throw new DriverException();
		} finally {
			try {
				
				if (this.conexao != null) this.conexao.fecharConexao();
				if (this.statement != null) this.statement.close();
				
			} catch (SQLException e) {
				throw new ConexaoException();
			}
		}
	}

	public void editar(Orders _orderid) throws ConexaoException,DriverException {
		PreparedStatement update = null;
		try {
			this.conexao.abrirConexao();
			this.statement = this.conexao.getConexao().prepareStatement(this.SQL_SELECT_BY_CODIGO);
			
			this.statement.setInt(1, _orderid.getOrderId());
			
			this.rs = statement.executeQuery();

			if (rs.next()) {
				update = this.conexao.getConexao().prepareStatement(this.SQL_UPDATE);

				update.setString(1, _orderid.getCustomerId());
				update.setInt(2, _orderid.getEmployeeId());
				update.setDate(3, _orderid.getOrderDate());
				update.setDate(4, _orderid.getRequiredDate());
				update.setDate(5, _orderid.getShippedDate());
				update.setInt(6, _orderid.getShipVia());
				update.setDouble(7, _orderid.getFreight());
				update.setString(8, _orderid.getShipName());
				update.setString(9, _orderid.getShipAddress());
				update.setString(10, _orderid.getShipCity());
				update.setString(11, _orderid.getShipRegion());
				update.setString(12, _orderid.getShipPostalCode());
				update.setString(13, _orderid.getShipCountry());

				update.execute();
				update.close();
			}
		} catch (SQLException e) {
			throw new ConexaoException();
		} catch (ClassNotFoundException e) {
			throw new DriverException();
		} finally {
			try {
				if (this.conexao != null) this.conexao.fecharConexao();
				if (this.statement != null) this.statement.close();
				if (this.rs != null) this.rs.close();
				if (update != null) update.close();
			} catch (SQLException e) {
				throw new ConexaoException();
			}
		}

	}

	public void excluir(Integer _orderid) throws ConexaoException,DriverException {
		try {
			this.conexao.abrirConexao();
			this.statement = this.conexao.getConexao().prepareStatement(this.SQL_DELETE);
			
			this.statement.setInt(1, _orderid);
			
			this.statement.execute();

		} catch (SQLException e) {
			throw new ConexaoException();
		} catch (ClassNotFoundException e) {
			throw new DriverException();
		} finally {
			try {
				
				if (this.conexao != null) this.conexao.fecharConexao();
				if (this.statement != null) this.statement.close();
				if (this.rs != null) this.rs.close();
				
			} catch (SQLException e) {
				throw new ConexaoException();
			}
		}
	}

	public List<Orders> selecionarAll() throws ConexaoException,DriverException {
		List<Orders> listaDeOrders = new ArrayList<Orders>();
		try {
			this.conexao.abrirConexao();
			this.statement = this.conexao.getConexao().prepareStatement(this.SQL_SELECT_TODOS);
			this.rs = this.statement.executeQuery();

			while (rs.next()) {
				
				int OrderId = this.rs.getInt(1);
				String CustomerId = this.rs.getString(2);
				int EmployeeId = this.rs.getInt(3);
				Date OrderDate = this.rs.getDate(4);
				Date RequiredDate = this.rs.getDate(5);
				Date ShippedDate = this.rs.getDate(6);
				int ShipVia = this.rs.getInt(7);
				double Freight = this.rs.getDouble(8);
				String ShipName = this.rs.getString(9);
				String ShipAddress = this.rs.getString(10);
				String ShipCity = this.rs.getString(11);
				String ShipRegion = this.rs.getString(12);
				String ShipPostalCode = this.rs.getString(13);
				String ShipCountry = this.rs.getString(14);
				
				listaDeOrders.add(new Orders(OrderId, CustomerId, EmployeeId, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry));
				
			}

		} catch (SQLException e) {
			throw new ConexaoException();
		} catch (ClassNotFoundException e) {
			throw new DriverException();
		} finally {
			try {
				if (this.conexao != null) this.conexao.fecharConexao();
				if (this.statement != null) this.statement.close();
			} catch (SQLException e) {
				throw new ConexaoException();
			}
		}
		return listaDeOrders;
	}

	public Orders selecionarByCod(Integer _customerid) throws ConexaoException, DriverException {
		Orders localizado = null;
		try {
			this.conexao.abrirConexao();
			this.statement = this.conexao.getConexao().prepareStatement(this.SQL_SELECT_BY_CODIGO);
			this.statement.setInt(1, _customerid);
			this.rs = this.statement.executeQuery();

			if (rs.next()) {
				
				int OrderId = this.rs.getInt(1);
				String CustomerId = this.rs.getString(2);
				int EmployeeId = this.rs.getInt(3);
				Date OrderDate = this.rs.getDate(4);
				Date RequiredDate = this.rs.getDate(5);
				Date ShippedDate = this.rs.getDate(6);
				int ShipVia = this.rs.getInt(7);
				double Freight = this.rs.getDouble(8);
				String ShipName = this.rs.getString(9);
				String ShipAddress = this.rs.getString(10);
				String ShipCity = this.rs.getString(11);
				String ShipRegion = this.rs.getString(12);
				String ShipPostalCode = this.rs.getString(13);
				String ShipCountry = this.rs.getString(14);
				
				localizado = new Orders(OrderId, CustomerId, EmployeeId, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry);
				
			}

		} catch (SQLException e) {
			throw new ConexaoException();
		} catch (ClassNotFoundException e) {
			throw new DriverException();
		} finally {
			try {
				if (this.conexao != null) this.conexao.fecharConexao();
				if (this.statement != null) this.statement.close();
			} catch (SQLException e) {
				throw new ConexaoException();
			}
		}
		return localizado;
	}

}
